{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "pd.set_option('display.width', 1000)\n",
    "\n",
    "df_origin = pd.read_csv(r'H:\\File\\RQDATA\\笔试题目\\algoActual.csv')\n",
    "df_origin = df_origin.replace('\\t','', regex=True)\n",
    "df = df_origin.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "1. 每个股票的买/卖股票数量，金额，盈亏和收益率。\n",
    "2. 每个股票总下单数， 撤单和成交的比例。\n",
    "3. 每个股票交易费用，占盈亏的比例是多少？\n",
    "4. 是否有没有平掉的仓位？如有， 是那个股票？ 多少股？\n",
    "5. 所有的股票，总的交易的金额， 按时间分布， 每10分钟的成交金额和收益是如何？\n",
    "6. 所有的股票，成交价格在5块钱以下的， 收益， 收益率， 赚钱、亏钱的股票个数是多少， 赚钱/亏钱总额是多少？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.1 每个股票的买/卖股票数量\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol     Side\n",
       "000001.SZ  B          0\n",
       "           S       6100\n",
       "000002.SZ  S       4500\n",
       "000004.SZ  S       1200\n",
       "000009.SZ  B       3500\n",
       "                   ... \n",
       "688700.SH  B        400\n",
       "           S        400\n",
       "688777.SH  B        400\n",
       "688819.SH  B        600\n",
       "688981.SH  B       5000\n",
       "Name: CumQty, Length: 1456, dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['Symbol','Side']).CumQty.sum() #每个股票的买/卖股票数量"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.2 每个股票的买/卖股票金额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol     Side\n",
       "000001.SZ  B            0.0\n",
       "           S       108997.0\n",
       "000002.SZ  S        94045.0\n",
       "000004.SZ  S        27543.0\n",
       "000009.SZ  B        75086.0\n",
       "                     ...   \n",
       "688700.SH  B        18368.0\n",
       "           S        18196.0\n",
       "688777.SH  B        45584.0\n",
       "688819.SH  B        32886.0\n",
       "688981.SH  B       323654.0\n",
       "Name: Amount, Length: 1456, dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Amount'] = df['CumQty'] * df['Price'] #成交量*成交价 = 成交额 AvgPx与成交价差价要求计入手续费，用下单价计算成交额 用于后续计算盈利\n",
    "df.groupby(['Symbol','Side']).Amount.sum() #每个股票的买/卖股票金额"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.3 每个股票的买/卖股票盈亏"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df.Side!='S','Amount'] = df.loc[df.Side!='S','Amount']*-1 #买方向成交额取反\n",
    "df['Side_count'] = df.groupby(['Symbol'])['Side'].transform(lambda x:x.nunique()) #Side_count计算每个股票下买和卖是否都出现 2是1否 sidecount=1则的品种不计盈利\n",
    "df['BS_Qty_for_Side_count'] = df.groupby(['Symbol','Side']).CumQty.transform('sum') #BS_Qty_for_Side_count为计算每个股票的每个交易方向上成交量之和\n",
    "df['BS_Qty_for_Side_count'] = df.groupby('Symbol').BS_Qty_for_Side_count.transform('cumprod')  #修正BS_Qty_for_Side_count为每个股票下每个交易方向成交量之积，如果为0，则说明该品种存在某个方向交易行为无效\n",
    "\n",
    "df.loc[(df['Side_count']==2)&(df['BS_Qty_for_Side_count']==0),'Side_count'] = 1 #将所有Side_count为2，买和卖都出现，但存在某个方向交易行为无效的情况，修订Side_count为1\n",
    "\n",
    "df['BS_Qty_for_last_Side'] = df.groupby(['Symbol','Side']).CumQty.transform(lambda x:x.abs().sum()) #每个股票的买/卖成交量\n",
    "df.loc[df.Side!='S','CumQty'] = df.loc[df.Side!='S','CumQty']*-1 #买方向成交量取反\n",
    "\n",
    "df['Open_Position'] = df.groupby(['Symbol']).CumQty.transform('cumsum') #得到每次交易时的未平仓量 \n",
    "df['Last_Cover_Point'] = df.groupby(['Symbol']).Open_Position.apply(lambda x:x.rolling(2).apply(lambda x:x.prod())) #Last_Cover_Point列最后为负值的行是该品种上一次的平仓点\n",
    "\n",
    "temp = df.loc[df.Last_Cover_Point <= 0].groupby('Last_Cover_Point').last() #如果Open_Position最后刚好为0 必须考虑等于情况\n",
    "temp['Last_Cover_Point_flag'] = 1\n",
    "df =pd.merge(df,temp,how = 'left')\n",
    "\n",
    "df['Last_Cover_Point_flag'] = df.groupby('Symbol').Last_Cover_Point_flag.apply(lambda x:x.fillna(method='bfill')) #用1后值填充nan点\n",
    "df['Last_Cover_Point_flag'].fillna(-1,inplace=True) #其余nan为-1,在Last_Cover_Point_flag列的最后一个1是上一次平仓点\n",
    "df['Symbol_Pnl'] = df.loc[(df.Last_Cover_Point_flag==1) & (df.Side_count==2)].groupby('Symbol').Amount.transform('cumsum') #获取所有有有效双向交易的品种并对Last_Cover_Point_flag标识为1的成交额取累加计算（需要修正可能开仓成交额\n",
    "\n",
    "df['Symbol_Pnl_fix'] = df['Price'] * df['Open_Position'] #获得修正开仓成交额 \n",
    "df['Symbol_Pnl_Final'] = df['Symbol_Pnl'] - df['Symbol_Pnl_fix']\n",
    "\n",
    "df['Symbol_Pnl_Final_T'] = df.loc[(df.Side_count==2) & (df.Last_Cover_Point_flag==1)].groupby('Symbol')['Symbol_Pnl_Final'].transform('last') #未平仓不计入盈亏"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol\n",
       "000155.SZ     76.00\n",
       "000158.SZ     32.00\n",
       "000572.SZ    513.00\n",
       "000612.SZ    275.00\n",
       "000825.SZ    269.00\n",
       "002078.SZ      4.00\n",
       "002109.SZ     -3.00\n",
       "002119.SZ     94.00\n",
       "002121.SZ    401.00\n",
       "002145.SZ    234.00\n",
       "002249.SZ     33.00\n",
       "002497.SZ    363.01\n",
       "002624.SZ    841.00\n",
       "300010.SZ     20.00\n",
       "300098.SZ     71.00\n",
       "300159.SZ      3.00\n",
       "300303.SZ   -311.00\n",
       "300311.SZ    112.00\n",
       "600166.SH     28.00\n",
       "600771.SH    -22.00\n",
       "601933.SH     -8.00\n",
       "603936.SH     53.00\n",
       "688595.SH    151.50\n",
       "Name: Symbol_Pnl_Final_T, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[(df.Side_count==2) & (df.Last_Cover_Point_flag==1)].groupby('Symbol')['Symbol_Pnl_Final_T'].last() #每个股票的买/卖股票盈亏"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1.4 每个股票的买/卖股票收益率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['for_Mini_BS_Qty'] = df.groupby(['Symbol','Side']).CumQty.transform(lambda x:x.abs().sum()) #获得每个股票买和卖的成交量绝对值\n",
    "df['for_Mini_BS_Qty'] = df.groupby('Symbol')['for_Mini_BS_Qty'].transform('min') #将for_Mini_BS_Qty，将每个股票下买和卖的成交量绝对值统一为两者较小值\n",
    "df['Cap_Ratio'] = df.loc[(df.Side_count==2) & (df.Last_Cover_Point_flag==1)].groupby('Symbol')['Price'].transform('last') #计算最后交易价格 #如果用avgpx，可能取到0\n",
    "df['Cap_Ratio'] = df['Cap_Ratio'] * df['for_Mini_BS_Qty'] #市值 = 最后交易价格 * 已平仓成交量  已较小值成交量作为已平仓成交量\n",
    "df['Cap_Ratio'] = df['Symbol_Pnl_Final_T'] /df['Cap_Ratio'] #收益率 = 盈亏 / 市值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol\n",
       "300303.SZ   -0.013727\n",
       "002109.SZ   -0.005310\n",
       "601933.SH   -0.001757\n",
       "600771.SH   -0.000565\n",
       "300159.SZ    0.000447\n",
       "002078.SZ    0.001138\n",
       "000155.SZ    0.001155\n",
       "000158.SZ    0.001248\n",
       "002249.SZ    0.005641\n",
       "688595.SH    0.006313\n",
       "600166.SH    0.007407\n",
       "300311.SZ    0.007775\n",
       "002119.SZ    0.009607\n",
       "300098.SZ    0.010457\n",
       "603936.SH    0.010643\n",
       "300010.SZ    0.012531\n",
       "002497.SZ    0.014819\n",
       "000572.SZ    0.023457\n",
       "000825.SZ    0.025707\n",
       "000612.SZ    0.027610\n",
       "002121.SZ    0.028715\n",
       "002145.SZ    0.029382\n",
       "002624.SZ    0.030359\n",
       "Name: Cap_Ratio, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df.Side_count==2].groupby('Symbol').Cap_Ratio.first().sort_values().dropna() #每个股票的买/卖股票收益率"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2 每个股票总下单数， 撤单和成交的比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Total_OrdQty'] = df.groupby('Symbol')['OrderQty'].transform('sum')\n",
    "df['Total_CumQty'] = df.groupby('Symbol')['CumQty'].transform(lambda x:x.abs().sum())\n",
    "\n",
    "df['Cum_Ratio'] = df['Total_CumQty']/df['Total_OrdQty']\n",
    "\n",
    "df['Canceled_Qty'] = df.OrderQty -abs(df.CumQty) #所有没成交的部分都暂时算为撤单量\n",
    "\n",
    "df['Total_CanceledQty'] = df.loc[(df['OrdStatus']=='Canceled')].groupby('Symbol')['Canceled_Qty'].transform('sum') #对存在撤单状态的每个品种的撤单量求和\n",
    "df['Canceled_Ratio'] = df['Total_CanceledQty']/df['Total_OrdQty'] #计算撤单率\n",
    "df.loc[df['OrdStatus']!='Canceled','Canceled_Ratio'] = np.nan #将所有非撤单状态的对应的撤单率等于nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol\n",
       "000001.SZ    6100\n",
       "000002.SZ    4500\n",
       "000004.SZ    1200\n",
       "000009.SZ    3500\n",
       "000012.SZ    1800\n",
       "             ... \n",
       "688699.SH     200\n",
       "688700.SH     800\n",
       "688777.SH     400\n",
       "688819.SH     600\n",
       "688981.SH    5000\n",
       "Name: CumQty, Length: 1052, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Symbol')['CumQty'].apply(lambda x:x.abs().sum()) #每个股票总下单数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol\n",
       "000001.SZ    0.516949\n",
       "000002.SZ    1.000000\n",
       "000004.SZ    0.923077\n",
       "000009.SZ    0.813953\n",
       "000012.SZ    1.000000\n",
       "               ...   \n",
       "688699.SH    1.000000\n",
       "688700.SH    1.000000\n",
       "688777.SH    1.000000\n",
       "688819.SH    0.750000\n",
       "688981.SH    0.892857\n",
       "Name: Cum_Ratio, Length: 1052, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('Symbol')['Cum_Ratio'].last() #成交比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol\n",
       "000001.SZ    0.483051\n",
       "000004.SZ    0.076923\n",
       "000009.SZ    0.186047\n",
       "000040.SZ    0.230769\n",
       "000060.SZ    0.119048\n",
       "               ...   \n",
       "688661.SH    0.415205\n",
       "688663.SH    0.333333\n",
       "688686.SH    1.000000\n",
       "688819.SH    0.250000\n",
       "688981.SH    0.107143\n",
       "Name: Canceled_Ratio, Length: 652, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df['OrdStatus']=='Canceled'].groupby('Symbol')['Canceled_Ratio'].last() #撤单比例"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3 每个股票交易费用，占盈亏的比例是多少"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-13-a65b8222a054>:7: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  df.groupby('Symbol')['Total_Extra_fee','Extra_fee_Ratio'].last().dropna() #每个股票交易费用，占盈亏的比例\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Total_Extra_fee</th>\n",
       "      <th>Extra_fee_Ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>000155.SZ</th>\n",
       "      <td>-8.278051</td>\n",
       "      <td>-0.108922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000158.SZ</th>\n",
       "      <td>31.989518</td>\n",
       "      <td>0.999672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000572.SZ</th>\n",
       "      <td>73.062113</td>\n",
       "      <td>0.142421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000612.SZ</th>\n",
       "      <td>8.637101</td>\n",
       "      <td>0.031408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>000825.SZ</th>\n",
       "      <td>13.689992</td>\n",
       "      <td>0.050892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002078.SZ</th>\n",
       "      <td>69.000474</td>\n",
       "      <td>17.250119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002109.SZ</th>\n",
       "      <td>14.863235</td>\n",
       "      <td>-4.954412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002119.SZ</th>\n",
       "      <td>13.269816</td>\n",
       "      <td>0.141168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002121.SZ</th>\n",
       "      <td>38.193578</td>\n",
       "      <td>0.095246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002145.SZ</th>\n",
       "      <td>49.382540</td>\n",
       "      <td>0.211036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002249.SZ</th>\n",
       "      <td>-11.536766</td>\n",
       "      <td>-0.349599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002497.SZ</th>\n",
       "      <td>-18.920922</td>\n",
       "      <td>-0.052122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>002624.SZ</th>\n",
       "      <td>34.709405</td>\n",
       "      <td>0.041272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300010.SZ</th>\n",
       "      <td>41.728012</td>\n",
       "      <td>2.086401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300098.SZ</th>\n",
       "      <td>-30.714830</td>\n",
       "      <td>-0.432603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300159.SZ</th>\n",
       "      <td>9.248776</td>\n",
       "      <td>3.082925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300303.SZ</th>\n",
       "      <td>29.598416</td>\n",
       "      <td>-0.095172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300311.SZ</th>\n",
       "      <td>58.392089</td>\n",
       "      <td>0.521358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600166.SH</th>\n",
       "      <td>29.193257</td>\n",
       "      <td>1.042616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600771.SH</th>\n",
       "      <td>58.093984</td>\n",
       "      <td>-2.640636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>601933.SH</th>\n",
       "      <td>6.298417</td>\n",
       "      <td>-0.787302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603936.SH</th>\n",
       "      <td>7.349601</td>\n",
       "      <td>0.138672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>688595.SH</th>\n",
       "      <td>30.485970</td>\n",
       "      <td>0.201228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Total_Extra_fee  Extra_fee_Ratio\n",
       "Symbol                                     \n",
       "000155.SZ        -8.278051        -0.108922\n",
       "000158.SZ        31.989518         0.999672\n",
       "000572.SZ        73.062113         0.142421\n",
       "000612.SZ         8.637101         0.031408\n",
       "000825.SZ        13.689992         0.050892\n",
       "002078.SZ        69.000474        17.250119\n",
       "002109.SZ        14.863235        -4.954412\n",
       "002119.SZ        13.269816         0.141168\n",
       "002121.SZ        38.193578         0.095246\n",
       "002145.SZ        49.382540         0.211036\n",
       "002249.SZ       -11.536766        -0.349599\n",
       "002497.SZ       -18.920922        -0.052122\n",
       "002624.SZ        34.709405         0.041272\n",
       "300010.SZ        41.728012         2.086401\n",
       "300098.SZ       -30.714830        -0.432603\n",
       "300159.SZ         9.248776         3.082925\n",
       "300303.SZ        29.598416        -0.095172\n",
       "300311.SZ        58.392089         0.521358\n",
       "600166.SH        29.193257         1.042616\n",
       "600771.SH        58.093984        -2.640636\n",
       "601933.SH         6.298417        -0.787302\n",
       "603936.SH         7.349601         0.138672\n",
       "688595.SH        30.485970         0.201228"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Extra_fee'] =(df['AvgPx'] - df['Price']) * df['CumQty'] #(成交价-下单价)*成交量 = 单次额外手续费\n",
    "df.loc[df.Side!='B','Extra_fee'] = df.loc[df.Side!='B','Extra_fee'] *-1  #当交易方向为卖时，手续费取反\n",
    "df['Extra_fee'] = df['Extra_fee'] + df['OtherFee'] + df['Fee']\n",
    "df['Total_Extra_fee'] = df.groupby('Symbol')['Extra_fee'].transform('sum')\n",
    "df['Extra_fee_Ratio'] = df['Total_Extra_fee']/df['Symbol_Pnl_Final_T']\n",
    "\n",
    "df.groupby('Symbol')['Total_Extra_fee','Extra_fee_Ratio'].last().dropna() #每个股票交易费用，占盈亏的比例"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4 是否有没有平掉的仓位？如有， 是那个股票？ 多少股？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Symbol\n",
       "000001.SZ   -6100\n",
       "000002.SZ   -4500\n",
       "000004.SZ   -1200\n",
       "000009.SZ    3500\n",
       "000012.SZ   -1800\n",
       "             ... \n",
       "688689.SH     400\n",
       "688699.SH     200\n",
       "688777.SH     400\n",
       "688819.SH     600\n",
       "688981.SH    5000\n",
       "Name: Open_Position, Length: 994, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = df.groupby('Symbol')['Open_Position'].last()*-1 #之前计算盈亏 买入量是负数 \n",
    "temp[temp!=0]  #每个股票未平仓数量"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5 所有的股票，总的交易的金额， 按时间分布， 每10分钟的成交金额和收益是如何？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['New_TransactTime'] = pd.to_datetime(df['TransactTime'],format=r'%H%M%S%f')\n",
    "df['New_TransactTime'] = df['New_TransactTime'] + (datetime.date(2021,8,3) - datetime.date(1900,1,1) )\n",
    "df_10min_Amount = df.resample(label='right', closed='right',rule='10min',on = 'New_TransactTime').Amount.apply(lambda x:x.abs().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "New_TransactTime\n",
       "2021-08-03 09:40:00     3642422.00\n",
       "2021-08-03 09:50:00      752705.65\n",
       "2021-08-03 10:00:00      883444.00\n",
       "2021-08-03 10:10:00    18532374.75\n",
       "2021-08-03 10:20:00     1077336.92\n",
       "2021-08-03 10:30:00      321649.00\n",
       "2021-08-03 10:40:00     3727302.00\n",
       "2021-08-03 10:50:00      918845.68\n",
       "2021-08-03 11:00:00      853707.00\n",
       "2021-08-03 11:10:00     1600946.94\n",
       "2021-08-03 11:20:00      429482.50\n",
       "2021-08-03 11:30:00      360027.36\n",
       "2021-08-03 13:10:00     1866487.00\n",
       "2021-08-03 13:20:00      482757.00\n",
       "2021-08-03 13:30:00      212426.00\n",
       "2021-08-03 13:40:00     1468537.00\n",
       "2021-08-03 13:50:00      317029.00\n",
       "2021-08-03 14:00:00      482697.75\n",
       "2021-08-03 14:10:00     1673562.00\n",
       "2021-08-03 14:20:00      463697.00\n",
       "2021-08-03 14:30:00      936448.00\n",
       "2021-08-03 14:40:00      885467.00\n",
       "2021-08-03 14:50:00      206187.00\n",
       "2021-08-03 15:00:00      217873.00\n",
       "Name: Amount, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_10min_Amount = df.resample(label='right', closed='right',rule='10min',on = 'New_TransactTime').Amount.apply(lambda x:x.abs().sum())\n",
    "df_10min_Amount[df_10min_Amount !=0] #10分钟成交额分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#收益时间分布\n",
    "condition_on_Pnl = (df.Last_Cover_Point_flag == 1) & (df.Side_count == 2)\n",
    "\n",
    "df['Symbol_Pnl_Final_Per_Pace'] = df.loc[condition_on_Pnl].groupby(['Symbol']).Symbol_Pnl_Final.apply(lambda x:x - x.shift(1))\n",
    "df_10min_Pnl = df.resample(label='right', closed='right',rule='10min',on = 'New_TransactTime').apply(lambda x:x.loc[condition_on_Pnl].groupby('Symbol')['Symbol_Pnl_Final_Per_Pace'].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "New_TransactTime\n",
       "2021-08-03 09:40:00     296.00\n",
       "2021-08-03 09:50:00    -140.00\n",
       "2021-08-03 10:00:00     -79.00\n",
       "2021-08-03 10:10:00    1256.01\n",
       "2021-08-03 10:20:00      14.00\n",
       "2021-08-03 10:30:00      90.00\n",
       "2021-08-03 10:40:00     854.00\n",
       "2021-08-03 10:50:00      -4.00\n",
       "2021-08-03 11:00:00     227.00\n",
       "2021-08-03 11:10:00     -76.00\n",
       "2021-08-03 11:20:00    -236.00\n",
       "2021-08-03 11:30:00     260.00\n",
       "2021-08-03 13:10:00       1.00\n",
       "2021-08-03 13:20:00     -10.00\n",
       "2021-08-03 13:30:00      24.00\n",
       "2021-08-03 13:40:00     401.50\n",
       "2021-08-03 13:50:00     132.00\n",
       "2021-08-03 14:10:00     265.00\n",
       "2021-08-03 14:20:00     -21.00\n",
       "2021-08-03 14:30:00      -6.00\n",
       "2021-08-03 14:40:00     -19.00\n",
       "Name: Symbol_Pnl_Final_Per_Pace, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_10min_Pnl.groupby('New_TransactTime').sum() ##10分钟收益分布"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6 所有的股票，成交价格在5块钱以下的， 收益， 收益率， 赚钱、亏钱的股票个数是多少， 赚钱/亏钱总额是多少？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-24-b8b3b76f1c85>:2: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  df_under_5 = df.loc[condition_under_5].groupby('Symbol')['Symbol_Pnl_Final_T','Cap_Ratio'].last()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Symbol_Pnl_Final_T</th>\n",
       "      <th>Cap_Ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>300010.SZ</th>\n",
       "      <td>20.0</td>\n",
       "      <td>0.012531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300098.SZ</th>\n",
       "      <td>71.0</td>\n",
       "      <td>0.010457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300159.SZ</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.000447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600166.SH</th>\n",
       "      <td>28.0</td>\n",
       "      <td>0.007407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>601933.SH</th>\n",
       "      <td>-8.0</td>\n",
       "      <td>-0.001757</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Symbol_Pnl_Final_T  Cap_Ratio\n",
       "Symbol                                  \n",
       "300010.SZ                20.0   0.012531\n",
       "300098.SZ                71.0   0.010457\n",
       "300159.SZ                 3.0   0.000447\n",
       "600166.SH                28.0   0.007407\n",
       "601933.SH                -8.0  -0.001757"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition_under_5 = (df.Price < 5) & ( df.Last_Cover_Point_flag == 1 )# & ( df.Side_count == 2)\n",
    "df_under_5 = df.loc[condition_under_5].groupby('Symbol')['Symbol_Pnl_Final_T','Cap_Ratio'].last()\n",
    "df_under_5 #成交价格在5块钱以下的， 收益， 收益率情况"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_under_5.loc[df_under_5.Symbol_Pnl_Final > 0].Symbol_Pnl_Final.count() #赚钱个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_under_5.loc[df_under_5.Symbol_Pnl_Final < 0].Symbol_Pnl_Final.count() #亏钱个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "121.99999999999989"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_under_5.loc[df_under_5.Symbol_Pnl_Final > 0].Symbol_Pnl_Final.sum() #赚钱总额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-8.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_under_5.loc[df_under_5.Symbol_Pnl_Final < 0].Symbol_Pnl_Final.sum() #亏钱总额"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3 (default, Jul  2 2020, 17:30:36) [MSC v.1916 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "5179d32cf6ec497baf3f8a3ef987cc77c5d2dc691fdde20a56316522f61a7323"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
